package com.wanmait.hotelManage.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.PseudoColumnUsage;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Timestamp;

import java.util.ArrayList;
import java.util.List;

import com.wanmait.hotelManage.util.DBHelper;
import com.wanmait.hotelManage.vo.Order;
import com.wanmait.hotelManage.vo.Pager;
import com.wanmait.hotelManage.vo.Register;
import com.wanmait.hotelManage.vo.RoomType;
import com.wanmait.hotelManage.vo.Userinfo;

public class OrderDAO {
	private OrderDAO() {};
	private static OrderDAO orderdao=new OrderDAO();
	public static OrderDAO getOrderdao() {
		return orderdao;
	}
	/**
	 * 登记表网上搜索内容返回
	 * @param findName
	 * @param findTelephone
	 * @return
	 */
	public void updremove(int id) {
		String sql=" delete from orders where id=? ";
		DBHelper.getDBHelper().executeUpdate(sql, id);
	}
	public boolean comment(int id) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		boolean b=true;
        String sql="SELECT DATEDIFF(NOW(),end_time)>0 c FROM orders  WHERE id=?";
        try {
        	conn=DBHelper.getDBHelper().getConn();
        	ps=conn.prepareStatement(sql);
        	ps.setInt(1, id);
        	rs=ps.executeQuery();
        	rs.next();
        	b=rs.getBoolean("c");
		} catch (Exception e) {

e.printStackTrace();
		}
		return b;
	}
	public Order SearchByNameAndTelephone(String findName, String findTelephone) {
		Order order=null;
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="SELECT * FROM orders,room_type WHERE NAME=? AND orders.telephone_number=? AND orders.room_type_id=room_type.id"
				+ " and orders.enable=1 and reg=0";
		conn=DBHelper.getDBHelper().getConn();
		List<Order>orders=new ArrayList();
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, findName);
			ps.setString(2, findTelephone);
			rs=ps.executeQuery();
			RoomType roomType=null;
			if(rs.next()){
				order=new Order();
				order.setName(findName);
				order.setTelephoneNumber(findTelephone);
				order.setInputTime(rs.getTimestamp("input_time"));
				order.setEndTime(rs.getTimestamp("end_time"));
				order.setStartTime(rs.getTimestamp("start_time"));
				order.setEnable(rs.getBoolean("orders.enable"));
				order.setBreakfast(rs.getBoolean("orders.breakfast"));
				roomType=new RoomType();
				roomType.setId(rs.getInt("room_type_id"));
				roomType.setTitle(rs.getString("room_type.title"));
				roomType.setDiscountPrice(rs.getDouble("room_type.discount_price"));
				roomType.setPrice(rs.getDouble("room_type.price"));
				roomType.setInputTime(rs.getTimestamp("room_type.input_time"));
				roomType.setPic(rs.getString("room_type.pic"));
				roomType.setDescription(rs.getString("room_type.description"));
				roomType.setEnable(rs.getBoolean("room_type.enable"));
				roomType.setHot(rs.getBoolean("room_type.hot"));
				order.setRoomType(roomType);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return order;
	}
	public List<Order> findAllOrder(Userinfo userinfo) {
		
	 Connection conn=null;
	 PreparedStatement ps=null;
	 ResultSet rs=null;
	 Order order=null;
	 StringBuilder sb=new StringBuilder();
	 List<Order>orders=new ArrayList();

	 sb.append(" SELECT *  FROM orders,room_type where userinfo_id=?   ")
	 .append(" and  room_type.id=orders.room_type_id and orders.enable=1  order by orders.input_time desc  ");
	 String sql=sb.toString();
	 try {
		conn=DBHelper.getDBHelper().getConn();
		ps=conn.prepareStatement(sql);
		ps.setInt(1, userinfo.getId());
		rs=ps.executeQuery();
		while(rs.next()) {
			 order=new Order();
			 RoomType roomType=new RoomType();
			 order.setId(rs.getInt("orders.id"));
			 order.setEndTime(rs.getTimestamp("end_time"));
			 order.setInputTime(rs.getTimestamp("input_time"));
			 order.setName(rs.getString("name"));
			 order.setStartTime(rs.getTimestamp("start_time"));
			 order.setTelephoneNumber(rs.getString("telephone_number"));
			 order.setCar(rs.getBoolean("car"));
			 order.setBreakfast(rs.getBoolean("breakfast"));
			 roomType.setTitle(rs.getString("room_type.title"));
			 roomType.setPrice(rs.getDouble("price"));
			 order.setRoomType(roomType);
			 order.setEnable(rs.getBoolean("orders.enable"));
			 
			 
			 
			 orders.add(order);
		}
	} catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
	}
	 finally {
		 DBHelper.getDBHelper().close(rs, ps, conn);
	 }
	 
	 return orders;
	}
	public void updataTui(int id) {
		
		String sql="UPDATE orders SET orders.enable =0 WHERE orders.id=?";
		DBHelper.getDBHelper().executeUpdate(sql, id);
	
	}
	/**
	 * 好像没用到
	 */
//	public void update(Order order) {
//		String sql="update orders set end_time=?,room_type_id=?,start_time=? where name=? and telephone_number=?";
//		Object[]params= {
//				order.getEndTime(),
//				order.getRoomType().getId(),
//				order.getStartTime(),
//				order.getName(),
//				order.getTelephoneNumber()
//		};
//		DBHelper.getDBHelper().executeUpdate(sql, params);
//	}
	/**
	 * 用户管理查找订单信息
	 * @return
	 */
	public List<Order> findall(Integer id,Pager pager) {
		List<Order>o=new ArrayList<Order>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT orders.id,orders.name,orders.telephone_number,orders.input_time,orders.breakfast,orders.car,orders.car_start_time,orders.car_end_time,orders.enable, ");
		sb.append(" orders.start_time,orders.end_time,room_type.discount_price,room_type.id,room_type.title FROM orders, ");
		sb.append(" room_type WHERE orders.room_type_id=room_type.id AND orders.userinfo_id=? limit ?,?");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			ps.setInt(1, id);
			ps.setInt(2, pager.getStart());
			ps.setInt(3, pager.getPageSize());
			rs=ps.executeQuery();
			Order order=null;
			RoomType roomtype=null;
			while(rs.next()) {
				order=new Order();
				order.setId(rs.getInt("orders.id"));
				order.setName(rs.getString("orders.name"));
				order.setTelephoneNumber(rs.getString("orders.telephone_number"));
				order.setInputTime(rs.getTimestamp("orders.input_time"));
				order.setStartTime(rs.getTimestamp("orders.start_time"));
				order.setEndTime(rs.getTimestamp("orders.end_time"));
				order.setBreakfast(rs.getBoolean("orders.breakfast"));
				order.setCar(rs.getBoolean("orders.car"));
				order.setCarStartTime(rs.getTimestamp("orders.car_start_time"));
				order.setCarEndTime(rs.getTimestamp("orders.car_end_time"));
				order.setEnable(rs.getBoolean("orders.enable"));
				roomtype=new RoomType();
				roomtype.setId(rs.getInt("room_type.id"));
				roomtype.setDiscountPrice(rs.getDouble("room_type.discount_price"));
				roomtype.setTitle(rs.getString("room_type.title"));
				order.setRoomType(roomtype);
				o.add(order);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return o;
	}
//	public static void main(String[] args) {
//		Pager pager=new Pager();
//		pager.setPageNo(1);
//		pager.setPageSize(4);
//		pager.setPageCount(11);
//		List<Order>o=OrderDAO.getOrderdao().findall(1, pager);
//		for (Order order : o) {
//			System.out.println(order.getRoomType().getDiscountPrice());
//		}
//	}
	public int getrows(Integer id) {
			Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			StringBuilder sb=new StringBuilder();
			sb.append(" select count(*) c  FROM orders,room_type WHERE orders.room_type_id=room_type.id AND orders.userinfo_id=? ");
			conn=DBHelper.getDBHelper().getConn();
			try {
				ps=conn.prepareStatement(sb.toString());
				ps.setInt(1, id);
				rs=ps.executeQuery();
				rs.next();
				Integer count=rs.getInt("c");
				return count;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				DBHelper.getDBHelper().close(rs, ps, conn);
			}
			return 0;
		}
	//下订单
	public void order(Userinfo userinfo, String name, String phone, String startTime, String endTime, String roomType,Boolean breakfast,Boolean car,String time, Timestamp tttt) {
		// TODO Auto-generated method stub
		String sql=" insert into orders(userinfo_id,name,telephone_number,"
				+"  start_time,end_time,breakfast,car_start_time,car_end_time,car,room_type_id)"
				+"  SELECT ?,?,?,?,?,?,?,?,?,id"
				+"   from room_type"
				+"   WHERE title=?";
		DBHelper.getDBHelper().executeUpdate(sql,userinfo.getId(),name,phone,startTime,endTime,breakfast,time,tttt,car,roomType);
	}
	
	public List<Order> getOrders(Pager pager)//今天和今天以后的订单
	{
		List<Order> list=new ArrayList<Order>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT * FROM orders, userinfo,room_type where orders.userinfo_id=userinfo.id and orders.room_type_id=room_type.id ");
		sb.append(" order by orders.input_time desc ");
		sb.append(" LIMIT ?,? ");
		String sql=sb.toString();
		try {
			conn = DBHelper.getDBHelper().getConn();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs = ps.executeQuery();
			while(rs.next())
			{
				Order order=new Order();
				order.setId(rs.getInt("id"));
				order.setName(rs.getString("name"));
				order.setTelephoneNumber(rs.getString("telephone_number"));
				order.setInputTime(rs.getTimestamp("input_time"));
				order.setStartTime(rs.getTimestamp("start_time"));
				order.setEndTime(rs.getTimestamp("end_time"));
				order.setBreakfast(rs.getBoolean("breakfast"));
				order.setCar(rs.getBoolean("car"));
				order.setCarStartTime(rs.getTimestamp("car_start_time"));
				order.setCarEndTime(rs.getTimestamp("car_end_time"));
				order.setEnable(rs.getBoolean("enable"));
				Userinfo userinfo=new Userinfo();
				userinfo.setId(rs.getInt("userinfo.id"));
				userinfo.setUsername(rs.getString("username"));
				order.setUserinfo(userinfo);
				
				RoomType roomtype=new RoomType();
				roomtype.setTitle(rs.getString("room_type.title"));
				order.setRoomType(roomtype);
				
				list.add(order);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();//����쳣����Ϣ
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return list;
	}
	public int getRows()//今天和今天以后的订单总的数量
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT COUNT(*) AS c FROM orders LEFT JOIN userinfo ON orders.userinfo_id=userinfo.id ");
		String sql=sb.toString();
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			rs.next();
			int rows=rs.getInt("c");
			return rows;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
	public List<Integer> findTimeStamp(Integer id) {
		List<Integer>o=new ArrayList<Integer>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="SELECT DATEDIFF(end_time,start_time) c FROM orders,room_type where orders.room_type_id=room_type.id and userinfo_id=?";
		conn = DBHelper.getDBHelper().getConn();
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				o.add(rs.getInt("c"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return o;
	}
//	public static void main(String[] args) {
//		List<Integer>o=OrderDAO.getOrderdao().findTimeStamp(1);
//		for (Integer integer : o) {
//			System.out.println(integer);
//		}
//	}
	public Double getPrice(Order order) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		String sql="";
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()) {
				return rs.getDouble("p");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
		return null;
	}
	//状态更改为已登记
	public void updateReg(Register register) {
		// TODO Auto-generated method stub
		String sql="update orders set reg=1 where name=? and telephone_number=?";
		DBHelper.getDBHelper().executeUpdate(sql, register.getName(),register.getTelephoneNumber());
	}
	public int getDays(Integer id) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		String sql=" select datediff(end_time,start_time) d from orders where id=?";
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()) {
				return rs.getInt("d");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}

		
		return 0;
	}
}
